﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Web;
using System.Web.Configuration;
using System.Web.Security;

/// <summary>
/// Summary description for SqlHelp
/// </summary>
public class SqlHelp
{
    private string SqlConnectionString = WebConfigurationManager.AppSettings["ConnectionString"];//ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    private SqlConnection cn;		//创建SQL连接       
    private SqlDataReader sdr;		//创建SQL数据读取器
    private SqlCommand cmd;			//创建SQL命令对象
    private SqlParameter param;     //创建SQL参数           
    private readonly string RETURNVALUE = "RETURNVALUE";
    private static string p_file = "";
    private static string p_pageInfo = "";
    //OLE数据连接对象
    private OleDbConnection sqlOLEConn = null;
   
    public SqlHelp()
    {
        //p_file = filename;
        //p_pageInfo = pageinfo;
    }
    public SqlHelp(string conStr)
    {
        SqlConnectionString = conStr;
    }

    /// <summary>
    /// 实例化数据连接字符串
    /// </summary>
    /// <returns>返回数据连接字符串</returns>
    public SqlConnection GetConn()
    {
        SqlConnection cn = new SqlConnection(SqlConnectionString);
        return cn;
    }
    /// <summary>
    /// 返回数据连接字符串
    /// </summary>
    /// <returns></returns>
    public string GetConnStr()
    {
        return SqlConnectionString;
    }

    /// <summary>
    /// 打开数据库连接
    /// </summary>
    public void Open()
    {
        #region
        try
        {
            cn = new SqlConnection(SqlConnectionString);
            cn.Open();
        }
        catch
        { }
        #endregion
    }

    /// <summary>
    /// 关闭数据库连接
    /// </summary>
    public void Close()
    {
        #region
        if (cn != null)
        {
            cn.Close();
            cn.Dispose();
        }
        #endregion
    }

    /// <summary>
    /// 获得SqlDataReader对象 使用完须关闭DataReader,关闭数据库连接
    /// </summary>
    /// <param name="strSql">sql语句</param>
    /// <returns></returns>
    public SqlDataReader GetDataReader(string strSql)
    {
        #region
        try
        {
            Open();
            cmd = new SqlCommand(strSql, cn);
            sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch
        { }
        finally
        {///关闭数据库的连接
            Close();
        }
        return sdr;
        #endregion
    }


    /// <summary>
    /// 获得SqlDataReader对象 使用完须关闭DataReader,关闭数据库连接
    /// </summary>
    /// <param name="strSql">sql语句</param>
    /// <returns></returns>
    public DataTable GetDataTable(string strSql, SqlParameter[] prams)
    {
        #region
        SqlDataAdapter da = CreateSQLDataAdapter(strSql, prams);
        DataTable dt = new DataTable();
        try
        {///读取数据
            da.Fill(dt);
            // string l_strlist = dtTable.Rows[0].ItemArray[1].ToString();
        }
        catch
        { }
        finally
        {///关闭数据库的连接
            Close();
        }
        return dt;
        #endregion
    }

    /// <summary>
    /// 2009-09-20
    /// 获得SqlDataReader对象 使用完须关闭DataReader,关闭数据库连接
    /// Taylor
    /// </summary>
    /// <param name="strSql">sql语句</param>
    /// <returns>以Table形式返回数据集</returns>
    public DataTable GetDataTable(string strSql)
    {
        #region
        SqlDataAdapter da = CreateSQLDataAdapter(strSql, null);
        DataTable dt = new DataTable();
        try
        {///读取数据
            da.Fill(dt);
        }
        catch
        { }
        finally
        {///关闭数据库的连接
            Close();
        }
        return dt;
        #endregion
    }

    /// <summary>
    /// 执行SQL不返回任何值
    /// </summary>
    /// <param name="strSql"></param>
    public void RunSqlStr(string strSql)
    {
        #region 执行SQL不返回任何值
        try
        {
            Open();
            cmd = new SqlCommand(strSql, cn);
            cmd.ExecuteNonQuery();
        }
        catch
        {
        }
        finally
        {
            Close();
        }
        #endregion
    }


    /// <summary>
    /// 执行SQL不返回任何值
    /// </summary>
    /// <param name="strSql"></param>
    public int RunSqlInt(string strSql)
    {
        #region 执行SQL不返回任何值
        int i = 0;
        try
        {
            Open();
            cmd = new SqlCommand(strSql, cn);
            i = cmd.ExecuteNonQuery();
        }
        catch
        { }
        finally
        {
            Close();
        }
        return i;
        #endregion
    }
    public int RunSqlCmdInt(SqlCommand GetCmd)
    {
        #region 执行SQL不返回任何值
        int i = 0;
        try
        {
            //Open();
            SqlParameter[] Prams = new SqlParameter[GetCmd.Parameters.Count];
            for (int j=0;j<GetCmd.Parameters.Count;j++)
            { Prams[j] = GetCmd.Parameters[j]; }
            cmd = CreateSQLCommand(GetCmd.CommandText.ToString(), Prams);
            i = cmd.ExecuteNonQuery();
        }
        catch
        { }
        finally
        {
            Close();
        }
        return i;
        #endregion
    }
    //2009-11-23
    public int RunCmd(SqlCommand GetCmd)
    {
        #region 执行SQL不返回任何值
        int i = 0;
        try
        {
            Open();
            i = GetCmd.ExecuteNonQuery();
        }
        catch
        { }
        finally
        {
            Close();
        }
        return i;
        #endregion
    }
    public int RunSqlInt(string strSql, SqlParameter[] prams)
    {
        #region
        int i = 0;
        //Open();
        try
        {
            cmd = CreateSQLCommand(strSql, prams);
            i = cmd.ExecuteNonQuery();
        }
        catch
        { }
        finally { Close(); }
        return i;

        #endregion
    }

    /// <summary>
    /// 执行SQL语句，并返回第一行第一列结果
    /// </summary>
    /// <param name="strSql">SQL语句</param>
    /// <returns></returns>
    public string RunSqlReturn(string strSql)
    {
        #region
        string strReturn = "";
        Open();
        try
        {
            cmd = new SqlCommand(strSql, cn);
            strReturn = cmd.ExecuteScalar().ToString();
        }
        catch
        {
        }
        finally { Close(); }

        return strReturn;
        #endregion
    }

    /// <summary>
    /// 执行SQL语句，并返回第一行第一列结果
    /// </summary>
    /// <param name="strSql">SQL语句</param>
    /// <returns></returns>
    public string RunSqlReturn(string strSql, SqlParameter[] prams)
    {
        #region
        string strReturn = "";
        //Open();
        try
        {
            cmd = CreateSQLCommand(strSql, prams);
            strReturn = cmd.ExecuteScalar().ToString();
        }
        catch
        { }
        finally { Close(); }
        return strReturn;

        #endregion
    }
    /// <summary>
    /// 执行存储过程
    /// </summary>2009-05-06
    /// <param name="procName">存储过程名称</param>
    /// <param name="prams">存储过程所需参数</param>
    /// <returns>返回存储过程返回值</returns>
    public string RunProcReturn(string procName, SqlParameter[] prams)
    {
        #region
        string strReturn = "";
        try
        {
            cmd = CreateCommand(procName, prams);
            strReturn = cmd.ExecuteScalar().ToString();
        }
        catch
        { }
        finally { Close(); }
        return strReturn;
        #endregion
    }

    /// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="procName">存储过程的名称</param>
    /// <returns>返回存储过程返回值</returns>
    public int RunProc(string procName)
    {
        #region
        int i = 0;
        try
        {
            cmd = CreateCommand(procName, null);
            cmd.ExecuteNonQuery();
            cmd.Transaction.Commit();
            i = (int)cmd.Parameters["ReturnValue"].Value;
        }
        catch
        {
            cmd.Transaction.Rollback();
        }
        finally
        {
            Close();
        }
        return i;

        #endregion
    }
    /// <summary>
    /// 将增删改通过存储过程执行
    /// </summary>
    /// <param name="SqlStr">要执行Sql语句</param>
    /// <returns></returns>
    public int RunSqlStrProc(string SqlStr)
    {
        #region
        int i = 0;
        SqlParameter[] Prams ={ new SqlParameter("@Str", SqlDbType.VarChar, 5000) };
        Prams[0].Value = SqlStr;
        try
        {
            cmd = CreateCommand("ExecuteSqlStr", Prams);
            cmd.ExecuteNonQuery();
            cmd.Transaction.Commit();
            i = (int)cmd.Parameters["ReturnValue"].Value;
        }
        catch
        {
            cmd.Transaction.Rollback();
        }
        finally
        {
            Close();
        }
        return i;

        #endregion
    }

    /// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="procName">存储过程名称</param>
    /// <param name="prams">存储过程所需参数</param>
    /// <returns>返回存储过程返回值</returns>
    public int RunProc(string procName, SqlParameter[] prams)
    {
        #region
        try
        {
            cmd = CreateCommand(procName, prams);
            cmd.ExecuteNonQuery();
            cmd.Transaction.Commit();
        }
        catch
        {
            cmd.Transaction.Rollback();
            return -2;
        }
        finally
        {
            Close();
        }
        int len = prams.Length - 1;
        if (cmd.Parameters[len].Value == DBNull.Value)
        {
            return -1;
        }
        else
        {
            return Convert.ToInt32(cmd.Parameters[len].Value.ToString());
        }

        #endregion
    }

    public int RunProcInt(string procName, SqlParameter[] prams)
    {
        #region
        int i = 0;
        try
        {
            cmd = CreateCommand(procName, prams);
            i = cmd.ExecuteNonQuery();
            cmd.Transaction.Commit();
        }
        catch
        {
            cmd.Transaction.Rollback();
        }
        finally
        {
            Close();
        }
        return i;
        #endregion
    }

    /// <summary>
    /// 执行存储过程返回DataReader对象
    /// </summary>
    /// <param name="procName">Sql语句</param>
    /// <param name="dataReader">DataReader对象</param>
    public SqlDataReader RunProc(string procName, SqlDataReader dataReader)
    {
        #region
        try
        {
            cmd = CreateCommand(procName, null);
            dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch
        {
        }
        finally
        {
            Close();
        }
        return dataReader;
        #endregion
    }


    /// <summary>
    /// 执行存储过程返回DataReader对象
    /// </summary>
    /// <param name="procName">Sql语句</param>
    /// <param name="dataReader">DataReader对象</param>
    public DataTable RunDataTableProc(string cmdText, SqlParameter[] prams)
    {
        #region
        SqlDataAdapter da = CreateProcDataAdapter(cmdText, prams);
        DataTable dt = new DataTable();
        try
        {
            ///读取数据
            da.Fill(dt);
            // string l_strlist = dtTable.Rows[0].ItemArray[1].ToString();
        }
        catch
        {
        }
        finally
        {
            ///关闭数据库的连接
            //da.Dispose();
            Close();
        }

        return dt;
        #endregion
    }
    public DataTable[] RunProcGetTables(string cmdText, SqlParameter[] prams)
    {
        #region

        //SqlDataAdapter da = CreateProcDataAdapter(cmdText, prams);
        DataSet dsAll = RunProcDataSet(cmdText, prams);
        DataTable[] Dt = new DataTable[dsAll.Tables.Count];
        for (int i = 0; i < dsAll.Tables.Count; i++)
        { Dt[i] = dsAll.Tables[i]; }
        return Dt;

        #endregion
    }
    /// <summary>
    /// 2009-10-13
    /// </summary>
    /// <param name="cmdText"></param>
    /// <param name="prams"></param>
    /// <returns>多Table数据集</returns>
    public DataSet RunProcDataSet(string cmdText, SqlParameter[] prams)
    {
        #region
        SqlDataAdapter da = CreateProcDataAdapter(cmdText, prams);
        DataSet dsAll = new DataSet();
        try
        {   ///读取数据
            da.Fill(dsAll);
        }
        catch
        { }
        finally
        {
            ///关闭数据库的连接
            Close();
        }

        return dsAll;
        #endregion
    }
    public DataTable[] GetTables(string StrSql)
    {
        #region
        SqlDataAdapter da = CreateSQLDataAdapter(StrSql, null);
        DataSet dsAll = new DataSet();
        try
        {   ///读取数据
            da.Fill(dsAll);
        }
        catch
        { }
        finally
        {   ///关闭数据库的连接
            Close();
        }
        DataTable[] Dt = new DataTable[dsAll.Tables.Count];
        for (int i = 0; i < dsAll.Tables.Count; i++)
        { Dt[i] = dsAll.Tables[i]; }
        return Dt;
        #endregion
    }
    public DataSet GetTables(string StrSql, SqlParameter[] Prams)
    {
        #region
        SqlDataAdapter da = CreateSQLDataAdapter(StrSql, Prams);
        DataSet dsAll = new DataSet();
        try
        {   ///读取数据
            da.Fill(dsAll);
        }
        catch
        { }
        finally
        {   ///关闭数据库的连接
            Close();
        }

        return dsAll;
        #endregion
    }
    /// <summary>
    /// 创建一个SqlDataAdapter对象，用此来执行存储过程
    /// </summary>
    /// <param name="procName">存储过程的名称</param>
    /// <param name="prams">存储过程所需参数</param>
    /// <returns>返回SqlDataAdapter对象</retur0ns>
    private SqlDataAdapter CreateProcDataAdapter(string procName, SqlParameter[] prams)
    {
        ///打开数据库连接
        Open();

        ///设置SqlDataAdapter对象
        SqlDataAdapter da = new SqlDataAdapter(procName, cn);
        da.SelectCommand.CommandType = CommandType.StoredProcedure;

        ///添加把存储过程的参数
        if (prams != null)
        {
            foreach (SqlParameter parameter in prams)
            {
                da.SelectCommand.Parameters.Add(parameter);
            }
        }

        ///添加返回参数ReturnValue
        ///////MASHA CHANGE 

        //da.SelectCommand.Parameters.Add(
        //    new SqlParameter(RETURNVALUE, SqlDbType.Int,4,ParameterDirection.ReturnValue,
        //    false,0,0,string.Empty, DataRowVersion.Default,null));

        ///返回创建的SqlDataAdapter对象

        return da;
    }

    /// <summary>
    /// 创建一个SqlDataAdapter对象，用此来执行SQL语句
    /// </summary>
    /// <param name="cmdText">SQL语句</param>
    /// <param name="prams">SQL语句所需参数</param>
    /// <returns>返回SqlDataAdapter对象</returns>
    private SqlDataAdapter CreateSQLDataAdapter(string cmdText, SqlParameter[] prams)
    {
        ///打开数据库连接
        Open();

        ///设置SqlDataAdapter对象
        SqlDataAdapter da = new SqlDataAdapter(cmdText, cn);

        ///添加把存储过程的参数
        if (prams != null)
        {
            foreach (SqlParameter parameter in prams)
            {
                da.SelectCommand.Parameters.Add(parameter);
            }
        }

        ///添加返回参数ReturnValue
        da.SelectCommand.Parameters.Add(
            new SqlParameter(RETURNVALUE, SqlDbType.Int, 4, ParameterDirection.ReturnValue,
            false, 0, 0, string.Empty, DataRowVersion.Default, null));

        ///返回创建的SqlDataAdapter对象
        return da;
    }

    /// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="procName">存储过程的名称</param>
    /// <param name="prams">存储过程所需参数</param>
    /// <param name="dataReader">DataReader对象</param>
    public SqlDataReader RunProc(string procName, SqlParameter[] prams, SqlDataReader dataReader)
    {
        #region
        try
        {
            cmd = CreateCommand(procName, prams);
            dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch
        {
        }
        return dataReader;
        #endregion
    }

    /// <summary>
    /// 创建一个SqlCommand对象以此来执行存储过程
    /// </summary>
    /// <param name="procName">存储过程的名称</param>
    /// <param name="prams">存储过程所需参数</param>
    /// <returns>返回SqlCommand对象</returns>
    private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
    {
        #region
        // 确认打开连接
        Open();
        SqlTransaction tx = cn.BeginTransaction();
        cmd = new SqlCommand(procName, cn);
        cmd.Transaction = tx;
        cmd.CommandType = CommandType.StoredProcedure;

        // 依次把参数传入存储过程
        if (prams != null)
        {
            foreach (SqlParameter parameter in prams)
                cmd.Parameters.Add(parameter);
        }

        // 加入返回参数
        cmd.Parameters.Add(
            new SqlParameter("ReturnValue", SqlDbType.Int, 4,
            ParameterDirection.ReturnValue, false, 0, 0,
            string.Empty, DataRowVersion.Default, null));

        return cmd;
        #endregion
    }

    /// <summary>
    /// 创建一个SqlCommand对象以此来执行存储过程
    /// </summary>
    /// <param name="cmdText">SQL语句</param>
    /// <param name="prams">SQL语句所需参数</param>
    /// <returns>返回SqlCommand对象</returns>
    private SqlCommand CreateSQLCommand(string cmdText, SqlParameter[] prams)
    {
        ///打开数据库连接
        Open();
        SqlTransaction tx = cn.BeginTransaction();
        ///设置Command
        ///
        SqlCommand cmd = new SqlCommand(cmdText, cn);
        cmd.Transaction = tx;
        ///添加把存储过程的参数
        if (prams != null)
        {
            foreach (SqlParameter parameter in prams)
            {
                cmd.Parameters.Add(parameter);
            }
        }

        ///添加返回参数ReturnValue
        cmd.Parameters.Add(
            new SqlParameter(RETURNVALUE, SqlDbType.Int, 4, ParameterDirection.ReturnValue,
            false, 0, 0, string.Empty, DataRowVersion.Default, null));
        tx.Commit();
        ///返回创建的SqlCommand对象
        return cmd;
    }

    /// <summary>
    /// 传入输入参数
    /// </summary>
    /// <param name="ParamName">存储过程名称</param>
    /// <param name="DbType">参数类型</param></param>
    /// <param name="Size">参数大小</param>
    /// <param name="Value">参数值</param>
    /// <returns>新的 parameter 对象</returns>
    public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
    {
        #region
        return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);

        #endregion
    }

    /// <summary>
    /// 传入返回值参数
    /// </summary>
    /// <param name="ParamName">存储过程名称</param>
    /// <param name="DbType">参数类型</param>
    /// <param name="Size">参数大小</param>
    /// <returns>新的 parameter 对象</returns>
    public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
    {
        #region
        return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
        #endregion
    }


    /// <summary>
    /// 生成存储过程参数
    /// </summary>
    /// <param name="ParamName">存储过程名称</param>
    /// <param name="DbType">参数类型</param>
    /// <param name="Size">参数大小</param>
    /// <param name="Direction">参数方向</param>
    /// <param name="Value">参数值</param>
    /// <returns>新的 parameter 对象</returns>
    public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
    {
        #region

        if (Size > 0)
            param = new SqlParameter(ParamName, DbType, Size);
        else
            param = new SqlParameter(ParamName, DbType);

        param.Direction = Direction;
        if (!(Direction == ParameterDirection.Output && Value == null))
            param.Value = Value;

        return param;
        #endregion
    }

    #region "Fill DropDownList"

    public bool FillDropDownList(System.Data.DataTable p_DataTable,
        ref System.Web.UI.WebControls.DropDownList p_DropDownList, int p_ColumnIndex, string p_IndexString)
    {
        try
        {
            int l_iCount;
            p_DropDownList.Items.Clear();
            System.Web.UI.WebControls.ListItem l_FirstListItem = new System.Web.UI.WebControls.ListItem(p_IndexString, "0");
            p_DropDownList.Items.Add(l_FirstListItem);
            for (l_iCount = 0; l_iCount <= p_DataTable.Rows.Count - 1; l_iCount++)
            {
                System.Web.UI.WebControls.ListItem l_listItem = new System.Web.UI.WebControls.ListItem(p_DataTable.Rows[l_iCount].ItemArray.GetValue(p_ColumnIndex).ToString(),
                    p_DataTable.Rows[l_iCount].ItemArray.GetValue(0).ToString());
                p_DropDownList.Items.Add(l_listItem);
            }
            return true;
        }
        catch
        {
            return false;
        }
    }

    public bool FillDropDownList(string p_SqlString, ref System.Web.UI.WebControls.DropDownList p_DropDownList,
        int p_ColumnIndex, string p_IndexString)
    {
        try
        {
            Open();
            SqlDataAdapter l_DataAdapter = new SqlDataAdapter(p_SqlString, cn);
            System.Data.DataTable l_DataTable = new System.Data.DataTable();
            l_DataAdapter.Fill(l_DataTable);
            if (cn.State == ConnectionState.Open)
                Close();
            l_DataAdapter.Dispose();

            return this.FillDropDownList(l_DataTable, ref p_DropDownList, p_ColumnIndex, p_IndexString);
        }
        catch
        {
            return false;
        }
    }

    #endregion

    public string GetReturnString(string p_SqlString)
    {
        try
        {

            Open();
            SqlDataAdapter l_DataAdapter = new SqlDataAdapter(p_SqlString, cn);
            System.Data.DataTable l_DataTable = new System.Data.DataTable();
            l_DataAdapter.Fill(l_DataTable);
            l_DataAdapter.Dispose();
            if (cn.State == ConnectionState.Open)
            {
                Close();
            }
            if (l_DataTable.Rows.Count > 0)
            {
                string l_count = l_DataTable.Rows[0].ItemArray.GetValue(0).ToString();
                l_DataTable.Dispose();
                return l_count;
            }
            else
            {
                return "";
            }

        }
        catch
        {

            return "";
        }
    }

    /// <summary>
    /// 执行SQL语句
    /// </summary>
    /// <param name="cmdText">SQL语句</param>
    /// <param name="dataSet">返回DataSet对象</param>
    public void RunSQL(string cmdText, ref DataSet dataSet)
    {
        if (dataSet == null)
        {
            dataSet = new DataSet();
        }
        ///创建SqlDataAdapter
        SqlDataAdapter da = CreateSQLDataAdapter(cmdText, null);

        try
        {
            ///读取数据
            da.Fill(dataSet);
        }
        catch
        {

        }
        finally
        {
            ///关闭数据库的连接
            Close();
        }
    }

    /// <summary>
    /// 执行SQL语句
    /// </summary>
    /// <param name="cmdText">SQL语句</param>		
    /// <param name="dataReader">返回DataTable对象</param>
    public void RunSQL(string cmdText, ref  DataTable dtTable)
    {
        if (dtTable == null)
        {
            dtTable = new DataTable();
        }
        ///创建SqlDataAdapter
        SqlDataAdapter da = CreateSQLDataAdapter(cmdText, null);

        try
        {
            ///读取数据
            da.Fill(dtTable);
        }
        catch
        {

        }
        finally
        {
            ///关闭数据库的连接
            Close();
        }
    }

    public int ExecuteCommand(string sql)
    {
        int num = 0;
        SqlTransaction tran = null;
        try
        {
            Open();
            tran = cn.BeginTransaction();
            SqlCommand command = new SqlCommand();
            command.Connection = cn;
            command.Transaction = tran;
            string[] qu = sql.Split(new char[] { ';' });
            if (qu.Length > 0)
            {
                for (int i = 0; i < qu.Length; i++)
                {
                    command.CommandText = qu[i];
                    num += command.ExecuteNonQuery();
                }
            }
            if (num > 0)
                tran.Commit();
            else
                tran.Rollback();
        }
        catch
        {
            if (tran != null)
                tran.Rollback();
            Close();
        }
        finally
        {
            Close();
        }
        return num;
    }

    #region 获取OLE DataTable方法
    /// <summary>
    /// 获取一个DataTable对象，根据OLECommand,超时时间设置为3分钟
    /// </summary>
    /// <param name="sqlCmd"></param>
    /// <returns></returns>
    public DataTable GetOLEDataTable(OleDbCommand sqlCmd)
    {
        lock (this)
        {
            try
            {
                sqlOLEConn = new OleDbConnection(SqlConnectionString);
                sqlCmd.Connection = sqlOLEConn;
                sqlCmd.CommandTimeout = 600;
                OleDbDataAdapter sqld = new OleDbDataAdapter(sqlCmd);
                DataTable dt = new DataTable();
                sqld.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                Log.WritLog(ex.ToString(), System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                return null;
            }
            finally
            {
                if (sqlOLEConn.State == ConnectionState.Open)
                    sqlOLEConn.Close();
                sqlOLEConn.Dispose();
            }
        }

    }

    /// <summary>
    /// 获取一个DataTable对象，根据表名
    /// </summary>
    /// <param name="tableName"></param>
    /// <returns></returns>
    public DataTable GetOLEDataTable(string tableName)
    {
        string sql = string.Format("select * from {0}", tableName);
        OleDbCommand sqlc = new OleDbCommand();
        sqlc.CommandText = sql;
        return GetOLEDataTable(sqlc);
    }

    /// <summary>
    /// 获取一个DataTable对象，根据表名
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="columnName">列名</param>
    /// <returns></returns>
    public DataTable GetOLEDataTable(string tableName, string columnName)
    {
        string sql = string.Format("select {0} from {1}", columnName, tableName);
        OleDbCommand sqlc = new OleDbCommand();
        sqlc.CommandText = sql;
        return GetOLEDataTable(sqlc);
    }
    #endregion
}